package core;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.Map.Entry;

import exception.ColumnException;
import exception.ColumnExistsException;
import exception.ConstraintException;
import exception.DataException;
import exception.DatabaseExistsException;
import exception.NoSuchColumnFoundException;
import exception.NoSuchDatabaseFoundException;
import exception.NoSuchTableFoundException;
import exception.TableExistsException;
import exception.UnknownActionException;

public class RDBMS implements IRDBMS {
	private LinkedHashMap<String, Database> databases;
	
	public RDBMS() {
		databases = new LinkedHashMap<String, Database>();
	}

	@Override
	public void init() {		
		Connection connection = null;  
        ResultSet resultSet = null;  
        Statement statement = null;  
        
		try {
			Class.forName("org.hsqldb.jdbcDriver").newInstance();
			connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/test", "sa", "");
			
			statement = connection.createStatement();  
			resultSet = statement.executeQuery("SELECT dbName, tableName FROM table_");
			while (resultSet.next()) {
				if(!databases.containsKey(resultSet.getString("dbName"))) {
					createDatabase(resultSet.getString("dbName"));
				}
				
				ArrayList<String> columns = new ArrayList<String>();
				ResultSet resultSetColumns = statement.executeQuery("SELECT dbName, tableName, columnName, position, defaultValue, dataType, characterLenght, isNull FROM column WHERE dbName = '"+ resultSet.getString("dbName") +"' AND tableName = '"+ resultSet.getString("tableName") +"'");
				while(resultSetColumns.next()) {
					columns.add(String.format("%s:%s:%s:%s:%s:%s", resultSetColumns.getString("columnName"), resultSetColumns.getString("position"), resultSetColumns.getString("defaultValue"), resultSetColumns.getString("dataType"), resultSetColumns.getString("characterLenght"), resultSetColumns.getString("isNull")));
				}
				resultSetColumns.close();
				
				if(!databases.get(resultSet.getString("dbName")).getTables().containsKey(resultSet.getString("tableName"))) {
					createTable(resultSet.getString("dbName"), resultSet.getString("tableName"), columns, null);
				}
			}
			
			resultSet.close();
			
			statement = connection.createStatement();  
			resultSet = statement.executeQuery("SELECT dbName, tableName FROM table_");
			while (resultSet.next()) {
				Database database = databases.get(resultSet.getString("dbName"));
				Table table = database.getTables().get(resultSet.getString("tableName"));
				
				HashMap<String, ArrayList<String>> constraints = new HashMap<String, ArrayList<String>>();
				
				ResultSet resultSetConstraints = statement.executeQuery("SELECT constraintName, type, columnName, referencedDbName, referencedTableName, referencedColumnName FROM constraints LEFT JOIN constraintsReferences ON constraints.constraintName = constraintsReferences.constraintName WHERE dbName = '"+ resultSet.getString("dbName") +"' AND tableName = '"+ resultSet.getString("tableName") +"' AND type = 'PRIMARY KEY'");
				while(resultSetConstraints.next()) {
					String head = String.format("%s:%s", resultSetConstraints.getString("constraintName"),resultSetConstraints.getString("type"));
					if(!constraints.containsKey(head)) {
						constraints.put(head, new ArrayList<String>());
					}
					
					constraints.get(head).add(String.format("%s|%s|%s|%s", resultSetConstraints.getString("columnName"), resultSetConstraints.getString("referencedDbName"), resultSetConstraints.getString("referencedTableName"), resultSetConstraints.getString("referencedColumnName")));
				}
				
				ArrayList<String> cons = new ArrayList<String>();
				for(String s:constraints.keySet()){
					String con = s;
					for(String s2:constraints.get(s)){
						con += ":"+s2;
					}
					cons.add(con);

				}
				table.addConstraints(this, cons);
			}
			
			statement = connection.createStatement();
			resultSet = statement.executeQuery("SELECT dbName, tableName FROM table_");
			while (resultSet.next()) {
				Database database = databases.get(resultSet.getString("dbName"));
				Table table = database.getTables().get(resultSet.getString("tableName"));
				
				HashMap<String, ArrayList<String>> constraints = new HashMap<String, ArrayList<String>>();
				
				ResultSet resultSetConstraints = statement.executeQuery("SELECT constraintName, type, columnName, referencedDbName, referencedTableName, referencedColumnName FROM constraints LEFT JOIN constraintsReferences ON constraints.constraintName = constraintsReferences.constraintName WHERE dbName = '"+ resultSet.getString("dbName") +"' AND tableName = '"+ resultSet.getString("tableName") +"' AND type = 'FOREIGN KEY'");
				while(resultSetConstraints.next()) {
					String head = String.format("%s:%s", resultSetConstraints.getString("constraintName"),resultSetConstraints.getString("type"));
					if(!constraints.containsKey(head)) {
						constraints.put(head, new ArrayList<String>());
					}
					
					constraints.get(head).add(String.format("%s|%s|%s|%s", resultSetConstraints.getString("columnName"), resultSetConstraints.getString("referencedDbName"), resultSetConstraints.getString("referencedTableName"), resultSetConstraints.getString("referencedColumnName")));
				}
				ArrayList<String> cons = new ArrayList<String>();
				for(String s:constraints.keySet()){
					String con = s;
					for(String s2:constraints.get(s)){
						con += ":"+s2;
					}
					cons.add(con);
				}
				
				table.addConstraints(this, cons);
			}
		} catch (Exception e) {  
            e.printStackTrace();  
        } finally {  
            try {  
                resultSet.close();  
                statement.close();  
                connection.close();  
            } catch (Exception e) {  
                e.printStackTrace();  
            }  
        }
	}

	@Override
	public void createDatabase(String dbName) throws DatabaseExistsException {
		if(databases.get(dbName) != null) {
			throw new DatabaseExistsException("Database '"+ dbName +"' already exists");
		}
		
		databases.put(dbName, new Database(dbName));
	}

	@Override
	public void createTable(String dbName, String tableName, ArrayList<String> columns, ArrayList<String> constraints) throws TableExistsException, NoSuchDatabaseFoundException, NoSuchTableFoundException, NoSuchColumnFoundException, ColumnExistsException, ConstraintException, NumberFormatException, ColumnException {
		if(!databases.containsKey(dbName)) {
			throw new NoSuchDatabaseFoundException("Database '"+ dbName +"' does not exists");
		}
		
		try {
			Database database = databases.get(dbName);
			database.addTable(tableName);
			database.addColumn(tableName, columns);
			
			if(constraints != null) {
				database.addConstraints(this, tableName, constraints);
			}
		} catch(ColumnExistsException e) {
			Database database = databases.get(dbName);
			database.dropTable(this, tableName);
			
			throw e;
		}
	}

	@Override
	public void alterTable(String dbName, String tableName, String action, String columnName, String type) throws NoSuchDatabaseFoundException, NoSuchTableFoundException, NoSuchColumnFoundException, ColumnExistsException, UnknownActionException, ConstraintException, NumberFormatException, ColumnException {
		if(!databases.containsKey(dbName)) {
			throw new NoSuchDatabaseFoundException("Database '"+ dbName +"' does not exists");
		}
		
		databases.get(dbName).alterTable(tableName, action, columnName, type);
	}

	@Override
	public void dropTable(String dbName, String tableName) throws NoSuchDatabaseFoundException, NoSuchTableFoundException, ConstraintException {
		if(!databases.containsKey(dbName)) {
			throw new NoSuchDatabaseFoundException("Database '"+ dbName +"' does not exists");
		}
		
		databases.get(dbName).dropTable(this,tableName);
	}

	@Override
	public void insert(String dbName, String tableName, HashMap<String, String> values) throws NoSuchDatabaseFoundException, NoSuchTableFoundException, NoSuchColumnFoundException, DataException, ConstraintException {
		if(!databases.containsKey(dbName)) {
			throw new NoSuchDatabaseFoundException("Database '"+ dbName +"' does not exists");
		}
		
		databases.get(dbName).insert(tableName, values);
	}

	@Override
	public HashMap<String, String> select(String dbName, String tableName, String columns, String[] conditions) throws NoSuchDatabaseFoundException, NoSuchTableFoundException, NoSuchColumnFoundException, DataException {
		if(!databases.containsKey(dbName)) {
			throw new NoSuchDatabaseFoundException("Database '"+ dbName +"' does not exists");
		}
		
		return databases.get(dbName).select(tableName, columns, conditions);
	}

	@Override
	public void delete(String dbName, String tableName, String[] conditions) throws NoSuchDatabaseFoundException, NoSuchTableFoundException, NoSuchColumnFoundException, DataException {
		if(!databases.containsKey(dbName)) {
			throw new NoSuchDatabaseFoundException("Database '"+ dbName +"' does not exists");
		}
		
		databases.get(dbName).delete(tableName, conditions);
	}
	
	@Override
	public void update(String dbName, String tableName, String[] update, String[] conditions) throws NoSuchDatabaseFoundException, NoSuchTableFoundException, NoSuchColumnFoundException, DataException {
		if(!databases.containsKey(dbName)) {
			throw new NoSuchDatabaseFoundException("Database '"+ dbName +"' does not exists");
		}
		
		databases.get(dbName).update(tableName, update, conditions);		
	}

	public Column getColumn(String db, String table, String column) throws NoSuchDatabaseFoundException, NoSuchTableFoundException, NoSuchColumnFoundException {
		if(databases.containsKey(db)) {
			return databases.get(db).getColumn(table, column);
		} else {
			throw new NoSuchDatabaseFoundException("Database "+ db +" does not exists");
		}
	}

	// Controle om te kijken of de tabel een referentie heeft.
	public boolean isReferenced(Table table) {
		for(Database data:databases.values()){
			if(data.isReferenced(table)){
				return true;
			}
		}
		
		return false;
	}
	
	public LinkedList<Record> getRecords(String dbName, String tableName) {
		return databases.get(dbName).getTables().get(tableName).getRecords();
	}
	
	public int getNumberOfDatabases() {
		return databases.size();
	}
	
	public int getNumberOfTables(String database){
		return databases.get(database).getNumberOfTables();
	}
	
	public Table getTable(String database,String table){
		return databases.get(database).getTables().get(table);
	}
	
	// Tijdelijke methode voor het debuggen
	public void print() {
		for(Entry<String, Database> database : databases.entrySet()) {
			System.out.println("Database: "+ database.getKey());
			
			for(Entry<String, Table> table : database.getValue().getTables().entrySet()) {
				System.out.println("Table: "+ table.getKey());
				
				for(Entry<String, Column> entry : table.getValue().getColumns().entrySet()) {
					System.out.println("Column: "+ entry.getValue());
				}
				
				System.out.println("======= CONSTRAINTS =======");
				
				for(Entry<String, Constraint> entry : table.getValue().getConstraints().entrySet()) {
					Constraint constraint = entry.getValue();
					System.out.println("constraint: "+constraint.getConstraintName()+" - "+constraint.getType());
					
					for(ConstraintReference reference : constraint.getContraintReference()) {
						System.out.println("Reference: "+ reference.getColumn() +" > "+ reference.getRefColumn());
					}
				}
				
				System.out.println("======== RECORDS ========");
				for(Record record : table.getValue().getRecords()) {
					System.out.println(record);
				}
				
				System.out.println("> --------------- <");
			}
			
			System.out.println("-----------------");
		}
	}
}